********************************************************************************
* Input files: data_relatives, data_coworkers
* Output file: List of coworkers flagged if they have a relative at the workplace
********************************************************************************

* Create dataset with index worker, workplace of the index worker, and a list 
* of all relatives of the index worker
use Data/data_relatives, clear
merge m:1 PersonId using Data/data_coworkers, keepusing(WorkplaceId)
drop if _merge == 1
drop _merge
rename PersonId IndexPersonId
save Data/tmp_list_of_relatives1, replace

* Create a similar dataset but only the list of relatives of all coworkers, i.e.,
* no connection to the index worker of the relative
drop IndexPersonId
rename Relative PersonId
duplicates drop
save Data/tmp_list_of_relatives2, replace

* Each case/coworker that match is a relative of an index worker and vice versa
* Use data_coworkers instead of data_coworkers_at_workplace
use Data/data_coworkers, clear
keep PersonId WorkplaceId
merge 1:1 WorkplaceId PersonId using Data/tmp_list_of_relatives2
keep if _merge == 3
drop _merge
rename PersonId Relative
save Data/tmp_list_of_RelativesOfIndexWorkers, replace

* Find which index workers have a relative at the workplace
use Data/tmp_list_of_RelativesOfIndexWorkers, clear
merge 1:m Relative WorkplaceId using Data/tmp_list_of_relatives1
keep if _merge == 3
drop _merge
drop Relative
rename IndexPersonId PersonId
duplicates drop
save Data/tmp_list_of_coworkers_with_a_relative_at_WP, replace

* Create the dataset with all coworkers where coworkers who have a relative at the workplace are flagged
use Data/data_coworkers, clear
keep PersonId WorkplaceId

* i) Flag: is a relative of another coworker
rename PersonId Relative
merge 1:1 WorkplaceId Relative using Data/tmp_list_of_RelativesOfIndexWorkers
gen IsRelativeOfACoworker = 0
replace IsRelativeOfACoworker = 1 if _merge == 3
drop _merge
rename Relative PersonId

* ii) Flag: has a coworker who is a relative
merge 1:1 WorkplaceId PersonId using Data/tmp_list_of_coworkers_with_a_relative_at_WP
gen HasACoworkerWhoIsARelative = 0
replace HasACoworkerWhoIsARelative = 1 if _merge == 3
drop _merge

* We only need HasACoworkerWhoIsARelative
drop IsRelativeOfACoworker

save Data/data_coworkers_with_relatives_who_are_coworkers, replace
